﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DBUtility;
using System.Data;

namespace SQLServerDAL
{
    public class IntegralBase
    {

        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public IntegralBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        #region 产品信息添加
        /// <summary>
        /// 产品信息添加
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string CreateIntegral(Model.IntegralHistoryBase integral)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@CreatedDate", SqlDbType.DateTime),				
					new SqlParameter("@ComesFrom", SqlDbType.NVarChar,200),
                    new SqlParameter("@IntegralCount", SqlDbType.Int,4),
                    new SqlParameter("@Id",SqlDbType.Int,4,"Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };

            Parms[0].Value =new Guid(integral.UserId);
            Parms[1].Value = integral.ExpirationDate;
            Parms[2].Value = integral.CreatedDate;
            Parms[3].Value = integral.ComesFrom;
            Parms[4].Value = integral.IntegralCount;
            Parms[5].Direction = ParameterDirection.Output;
            Parms[6].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "IntegralBase_Create", Parms);
                status = ((Parms[6].Value != null) ? ((int)Parms[6].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    //integral.Id = Parms[5].Value;
                    result += "|" + integral.Id;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 产品信息修改
        /// <summary>
        /// 产品信息修改
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string ModifyIntegral(Model.IntegralHistoryBase integral)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ExpirationDate", SqlDbType.DateTime),
					new SqlParameter("@CreatedDate", SqlDbType.DateTime),				
					new SqlParameter("@ComesFrom", SqlDbType.NVarChar,200),
                    new SqlParameter("@IntegralCount", SqlDbType.Int,4),
                    new SqlParameter("@Id",SqlDbType.Int,4),
                    new SqlParameter("@ValidIntegralCount",SqlDbType.Int,4),
                    new SqlParameter("@IsValid",SqlDbType.SmallInt),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };

            Parms[0].Value = new Guid(integral.UserId);
            Parms[1].Value = integral.ExpirationDate;
            Parms[2].Value = integral.CreatedDate;
            Parms[3].Value = integral.ComesFrom;
            Parms[4].Value = integral.IntegralCount;
            Parms[5].Value = integral.Id;
            Parms[6].Value = integral.ValidIntegralCount;
            Parms[7].Value = integral.IsValid;
            Parms[8].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "IntegralBase_Update", Parms);
                status = ((Parms[8].Value != null) ? ((int)Parms[8].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    result += "|" + integral.Id;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 获取产品信息实体
        /// <summary>
        /// 获取产品信息实体
        /// </summary> 
        /// <returns></returns>
        public Model.IntegralHistoryBase GetIntegralObjById(string Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_IntegrationHistory WHERE Id = @Id AND DeletionStateCode=0");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Id", SqlDbType.Int,4) };
            parameters[0].Value = Id;

            Model.IntegralHistoryBase integral = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    integral = new Model.IntegralHistoryBase();
                    integral.Id = Convert.ToInt32(rd["Id"].ToString());
                    integral.UserId = rd["UserId"].ToString();
                    integral.UserName = rd["UserName"].ToString();
                    integral.ComesFrom = rd["ComesFrom"].ToString();
                    integral.IntegralCount = Convert.ToInt32(rd["IntegralCount"].ToString());
                    integral.ValidIntegralCount = Convert.ToInt32(rd["ValidIntegralCount"].ToString());
                    integral.IsValid = Convert.ToBoolean(rd["IsValid"]);
                    integral.CreatedDate = Convert.ToDateTime(rd["CreatedDate"].ToString());
                    integral.ExpirationDate = Convert.ToDateTime(rd["ExpirationDate"].ToString());

                }
            }
            catch
            {
                return null;
            }
            return integral;
        }
        #endregion

        #region 设置产品状态
        /// <summary>
        /// 设置产品状态
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public string SetProductStatus(string status, string productid)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StatusCode",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = Convert.ToInt16(status);
            Parms[1].Value = new Guid(productid);

            string res = "";
            try
            {
                res = SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ProductBase_UpdateStatusCode", Parms).ToString();
            }
            catch (Exception err)
            {
                res = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return res;

        }
        #endregion

        #region 修改商品价格数据
        /// <summary>
        /// 修改商品价格数据
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdatePrice(decimal marketprice, decimal price, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@Price",SqlDbType.Decimal),
                new SqlParameter("@MarketPrice",SqlDbType.Decimal),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = price;
            Parms[1].Value = marketprice;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET MarketPrice = @MarketPrice, Price = @Price WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion

        #region 修改商品库存设值
        /// <summary>
        /// 修改商品库存设值
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdateStockSettings(int stockamount, int status, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StockAmount",SqlDbType.Int),
                new SqlParameter("@StockStatus",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = stockamount;
            Parms[1].Value = status;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET StockAmount = @StockAmount, StockStatus = @StockStatus WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion

        #region 验证是否重复
        /// <summary>
        /// 验证是否重复
        /// </summary>
        /// <param name="categoryid">类别Id</param>
        /// <returns></returns>
        public bool IsExist(string name, string productId)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@ProductId", SqlDbType.UniqueIdentifier, 16), 
                new SqlParameter("@ProductName", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = new Guid(productId);
            parameters[1].Value = name;

            strSql.Append("SELECT COUNT(1) FROM ProductBase WHERE [ProductName]= @ProductName AND ProductId <> @ProductId");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (_obj != null)
            {
                if ((int)_obj > 0)
                    return true;
            }
            return false;
        }
        #endregion

        #region 删除产品信息
        /// <summary>
        /// 删除产品信息
        /// </summary>
        /// <param name="id">产品Id</param>
        /// <param name="isLogical">是否逻辑删除(True 逻辑删除，False 物理删除)</param>
        public string Delete(string id, bool isLogical)
        {
            string result = "";
            SqlParameter[] parameters = new SqlParameter[] {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@isLogical", SqlDbType.Bit),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            parameters[0].Value = Convert.ToInt32(id);
            parameters[1].Value = isLogical;
            parameters[2].Direction = ParameterDirection.ReturnValue;

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "IntegralBase_Delete", parameters);
                int status = ((parameters[2].Value != null) ? ((int)parameters[2].Value) : -1);
                switch (status)
                {
                    case 0:
                        result = "succeeded";
                        break;
                    default:
                        result = status.ToString();
                        break;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }

            return result;
        }
        #endregion
    }

    public class ExchangeBase
    {

        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public ExchangeBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        #region 产品信息添加
        /// <summary>
        /// 产品信息添加
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string CreateIntegral(Model.ExchangeHistoryBase integral)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserId", SqlDbType.UniqueIdentifier,16),					
					new SqlParameter("@ExchangeDate", SqlDbType.DateTime),				
					new SqlParameter("@ExchangeFor", SqlDbType.NVarChar,200),
                    new SqlParameter("@ExchangeCount", SqlDbType.Int,4),
                    new SqlParameter("@Id",SqlDbType.Int,4,"Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };

            Parms[0].Value =new Guid(integral.UserId);
            Parms[1].Value = integral.ExchangeDate;
            Parms[2].Value = integral.ExchangeFor;
            Parms[3].Value = integral.ExchangeCount;
            Parms[4].Direction = ParameterDirection.Output;
            Parms[5].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ExchangeHistory_Create", Parms);
                status = ((Parms[5].Value != null) ? ((int)Parms[5].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    //integral.Id = Parms[5].Value;
                    result += "|" + integral.Id;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 产品信息修改
        /// <summary>
        /// 产品信息修改
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string ModifyIntegral(Model.ExchangeHistoryBase integral)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@UserId", SqlDbType.UniqueIdentifier,16),					
					new SqlParameter("@ExchangeDate", SqlDbType.DateTime),				
					new SqlParameter("@ExchangeFor", SqlDbType.NVarChar,200),
                    new SqlParameter("@ExchangeCount", SqlDbType.Int,4),
                    new SqlParameter("@Id",SqlDbType.Int,4),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };

            Parms[0].Value =new Guid(integral.UserId);
            Parms[1].Value = integral.ExchangeDate;
            Parms[2].Value = integral.ExchangeFor;
            Parms[3].Value = integral.ExchangeCount;
            Parms[4].Value = integral.Id;
            Parms[5].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ExchangeHistory_Update", Parms);
                status = ((Parms[5].Value != null) ? ((int)Parms[5].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    result += "|" + integral.Id;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 获取产品信息实体
        /// <summary>
        /// 获取产品信息实体
        /// </summary> 
        /// <returns></returns>
        public Model.ExchangeHistoryBase GetIntegralObjById(string Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_ExchangeHistory WHERE Id = @Id AND DeletionStateCode=0");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Id", SqlDbType.Int, 4) };
            parameters[0].Value = Id;

            Model.ExchangeHistoryBase integral = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    integral = new Model.ExchangeHistoryBase();
                    integral.Id = Convert.ToInt32(rd["Id"].ToString());
                    integral.UserId =rd["UserId"].ToString();
                    integral.UserName = rd["UserName"].ToString();
                    integral.ExchangeFor = rd["ExchangeFor"].ToString();
                    integral.ExchangeCount = Convert.ToInt32(rd["ExchangeCount"].ToString());
                    integral.ExchangeDate = Convert.ToDateTime(rd["ExchangeDate"].ToString());
                }
            }
            catch
            {
                return null;
            }
            return integral;
        }
        #endregion

        #region 设置产品状态
        /// <summary>
        /// 设置产品状态
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public string SetProductStatus(string status, string productid)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StatusCode",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = Convert.ToInt16(status);
            Parms[1].Value = new Guid(productid);

            string res = "";
            try
            {
                res = SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ProductBase_UpdateStatusCode", Parms).ToString();
            }
            catch (Exception err)
            {
                res = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return res;

        }
        #endregion

        #region 修改商品价格数据
        /// <summary>
        /// 修改商品价格数据
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdatePrice(decimal marketprice, decimal price, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@Price",SqlDbType.Decimal),
                new SqlParameter("@MarketPrice",SqlDbType.Decimal),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = price;
            Parms[1].Value = marketprice;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET MarketPrice = @MarketPrice, Price = @Price WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion

        #region 修改商品库存设值
        /// <summary>
        /// 修改商品库存设值
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdateStockSettings(int stockamount, int status, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StockAmount",SqlDbType.Int),
                new SqlParameter("@StockStatus",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = stockamount;
            Parms[1].Value = status;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET StockAmount = @StockAmount, StockStatus = @StockStatus WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion

        #region 验证是否重复
        /// <summary>
        /// 验证是否重复
        /// </summary>
        /// <param name="categoryid">类别Id</param>
        /// <returns></returns>
        public bool IsExist(string name, string productId)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@ProductId", SqlDbType.UniqueIdentifier, 16), 
                new SqlParameter("@ProductName", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = new Guid(productId);
            parameters[1].Value = name;

            strSql.Append("SELECT COUNT(1) FROM ProductBase WHERE [ProductName]= @ProductName AND ProductId <> @ProductId");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (_obj != null)
            {
                if ((int)_obj > 0)
                    return true;
            }
            return false;
        }
        #endregion

        #region 删除产品信息
        /// <summary>
        /// 删除产品信息
        /// </summary>
        /// <param name="id">产品Id</param>
        /// <param name="isLogical">是否逻辑删除(True 逻辑删除，False 物理删除)</param>
        public string Delete(string id, bool isLogical)
        {
            string result = "";
            SqlParameter[] parameters = new SqlParameter[] {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@isLogical", SqlDbType.Bit),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            parameters[0].Value = Convert.ToInt32(id);
            parameters[1].Value = isLogical;
            parameters[2].Direction = ParameterDirection.ReturnValue;

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ExchangeHistory_Delete", parameters);
                int status = ((parameters[2].Value != null) ? ((int)parameters[2].Value) : -1);
                switch (status)
                {
                    case 0:
                        result = "succeeded";
                        break;
                    default:
                        result = status.ToString();
                        break;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }

            return result;
        }
        #endregion
    }
}

